/**
 * 
 */
package com.feib.stms.dao.jdbc;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;


import com.feib.stms.dao.BasePaymentFileContentDao;
import com.feib.stms.dao.jdbc.rowmapper.BaseRowMapper;
import com.feib.stms.model.BasePaymentFileContent;
import com.feib.stms.model.Group;
import com.feib.stms.model.UploadFile;
import com.feib.stms.pagination.PaginatedList;

/**
 * @author Jimmy Liu
 *
 */
public abstract class BasePaymentFileContentJdbcDao<T extends BasePaymentFileContent> extends BaseJdbcDao implements BasePaymentFileContentDao<T>
{
	/**
	 * Table 名稱
	 * @return
	 */
	protected abstract String getTableName();

	protected abstract BaseRowMapper<T> createRowMapper();

	public PaginatedList<T> queryForPaginatedListByPaymentFileContent(T entity, Date deliveryDateFrom, Date deliveryDateTo, List<String> fileFormats, List<String> constrainSecuritiesNos, PaginatedList<T> paginatedList) 
	{
		SqlQueryMapProvider sqlQueryMapProvider = this.getSqlQueryMapProviderByPaymentFileContent(entity, deliveryDateFrom, deliveryDateTo, fileFormats, constrainSecuritiesNos, paginatedList);

		PaginatedList<T> result =  this.queryForPaginatedList(sqlQueryMapProvider, createRowMapper(), paginatedList.getPageNumber(), paginatedList.getPageSize());

		/* 最後一頁，計算總計 */
		if (null != result && result.isLastPage())
		{
			sqlQueryMapProvider = getTotalSqlQueryMapProviderByPaymentFileContent(entity, deliveryDateFrom, deliveryDateTo, fileFormats, constrainSecuritiesNos);

			T totalObject = this.getSimpleJdbcTemplate().queryForObject(sqlQueryMapProvider.getSql(), createRowMapper(), sqlQueryMapProvider.getParameters());

			result.setTotalObject(totalObject);



			/* 計算彙總*/
			sqlQueryMapProvider = getTotalSumSqlQueryMapProviderByPaymentFileContent(entity, deliveryDateFrom, deliveryDateTo, fileFormats, constrainSecuritiesNos);

			T totalObject2 = this.getSimpleJdbcTemplate().queryForObject(sqlQueryMapProvider.getSql(), createRowMapper(), sqlQueryMapProvider.getParameters());

			result.setTotalObject2(totalObject2);


		}

		return result;
	}

	public SqlQueryMapProvider getTotalSqlQueryMapProviderByPaymentFileContent(T entity, Date deliveryDateFrom, Date deliveryDateTo, List<String> fileFormats, List<String> constrainSecuritiesNos)
	{

		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();

		sql.append("SELECT COUNT(*) AS RECORD_COUNT, SUM(sumRpt.NET_PAY) AS NET_PAY, SUM(sumRpt.NET_INCOME) AS NET_INCOME ");
		sql.append(", SUM(sumRpt.NET_INCOME - sumRpt.NET_PAY) AS NET_INCOME_PAY_DIFF");
		sql.append(", SUM(CASE WHEN sumRpt.Net_Pay > 0 THEN 1 ELSE 0 END) AS Net_Pay_COUNT");
		sql.append(", SUM(CASE WHEN sumRpt.NET_INCOME > 0 THEN 1 ELSE 0 END) AS NET_INCOME_COUNT");
		sql.append(", SUM(sumRpt.CHKSUM) AS CHKSUM, SUM(sumRpt.NET_PAY_CHKSUM) AS NET_PAY_CHKSUM , SUM(sumRpt.NET_INCOME_CHKSUM) AS NET_INCOME_CHKSUM ");
		sql.append(", SUM(sumRpt.ACCOUNT_NO_CHKSUM) AS ACCOUNT_NO_CHKSUM , SUM(sumRpt.CUSTOMER_ID_CHKSUM) AS CUSTOMER_ID_CHKSUM");

		SqlQueryMapProvider tmp = getSqlQueryMapProviderByPaymentFileContent(entity, deliveryDateFrom, deliveryDateTo, fileFormats, constrainSecuritiesNos, null);
		sql.append(" from ( ");
		sql.append(tmp.getSql());
		sql.append(" ) sumRpt ");

		parameters.putAll(tmp.getParameters());

		SqlQueryMapProvider sqlQueryMapProvider = new SqlQueryMapProvider(sql.toString(), parameters);

		logger.debug("{}", sqlQueryMapProvider);

		return sqlQueryMapProvider;
	}


	/**
	 * 
	 * @param entity
	 * @param deliveryDateFrom 交割日期起
	 * @param deliveryDateTo 交割日期迄
	 * @param fileFormats 上傳格式, 因程式是切開查詢，所以用不到。若未來需要整合查詢，則需要每個Detail Union All
	 * @param constrainSecuritiesNos 可查詢的分公司
	 * @param paginatedList
	 * @return
	 */
	public SqlQueryMapProvider getSqlQueryMapProviderByPaymentFileContent(T entity, Date deliveryDateFrom, Date deliveryDateTo, List<String> fileFormats, List<String> constrainSecuritiesNos, PaginatedList<T> paginatedList)
	{
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append("SELECT PFC.*");
		sql.append(", CT.CODE_NAME AS DEAL_RESULT_CODE_NAME");
		sql.append(", S.SECURITIES_NAME AS SECURITIES_NAME");
		sql.append(" FROM ").append(this.getTableName()).append(" PFC");
		sql.append(" LEFT JOIN CODE_TABLE CT ON CT.CODE_KIND = 'DEAL_RESULT_CODE' AND CT.CODE_VALUE = PFC.DEAL_RESULT_CODE");
		sql.append(" LEFT JOIN SECURITIES S ON S.SECURITIES_NO = PFC.SECURITIES_NO");
		sql.append(" WHERE 1= 1");

		// 可查詢的分公司
		if (CollectionUtils.isNotEmpty(constrainSecuritiesNos))
		{
			if (1 == constrainSecuritiesNos.size())
			{
				sql.append(" AND PFC.SECURITIES_NO = :SECURITIES_NO");
				parameters.put("SECURITIES_NO",constrainSecuritiesNos.get(0) );
			}
			else
			{
				sql.append(" AND PFC.SECURITIES_NO IN (:SECURITIES_NOS)");
				parameters.put("SECURITIES_NOS",constrainSecuritiesNos );
			}

		}

		/* 上傳日期 */
		if (null != deliveryDateFrom && null != deliveryDateTo)
		{
			// between 上傳起迄
			sql.append(" AND TO_CHAR(PFC.DELIVERY_DATE, 'YYYYMMDD') >= TO_CHAR(:DELIVERY_DATE_FROM, 'YYYYMMDD')");
			sql.append(" AND TO_CHAR(PFC.DELIVERY_DATE, 'YYYYMMDD') <= TO_CHAR(:DELIVERY_DATE_TO, 'YYYYMMDD')");
			parameters.put("DELIVERY_DATE_FROM", deliveryDateFrom );
			parameters.put("DELIVERY_DATE_TO", deliveryDateTo);

		}
		else if (null != deliveryDateFrom)
		{
			// >=上傳起
			sql.append(" AND TO_CHAR(PFC.DELIVERY_DATE, 'YYYYMMDD') >= TO_CHAR(:DELIVERY_DATE_FROM, 'YYYYMMDD') ");
			parameters.put("UPLOAD_DATE_FROM", deliveryDateFrom);

		}
		else if (null != deliveryDateTo)
		{
			// <= 上傳迄
			sql.append(" AND TO_CHAR(PFC.DELIVERY_DATE, 'YYYYMMDD') <= TO_CHAR(:DELIVERY_DATE_TO, 'YYYYMMDD') ");
			parameters.put("DELIVERY_DATE_TO", deliveryDateTo);

		}
		else if (null != entity && null != entity.getDeliveryDate())
		{
			// 單一日期
			sql.append(" AND TO_CHAR(PFC.DELIVERY_DATE, 'YYYYMMDD') = TO_CHAR(:DELIVERY_DATE, 'YYYYMMDD' )");
			parameters.put("DELIVERY_DATE", entity.getDeliveryDate());

		}

		if (null != entity)
		{

			/* 營業員 */
			if (StringUtils.isNotEmpty(entity.getTeller()))
			{
				sql.append(" AND PFC.TELLER like :TELLER");
				parameters.put("TELLER", entity.getTeller()+"%" );
			}

			/* 客戶戶名 */
			if (StringUtils.isNotEmpty(entity.getCustomerName()))
			{
				sql.append(" AND PFC.CUSTOMER_NAME like :CUSTOMER_NAME");
				parameters.put("CUSTOMER_NAME", entity.getCustomerName()+"%" );
			}

			/* 所屬檔案 */
			UploadFile uploadFile = entity.getUploadFile();
			if (null != uploadFile &&  null != uploadFile.getUploadFilePk())
			{
				sql.append(" AND PFC.UPLOAD_FILE_PK = :UPLOAD_FILE_PK");
				parameters.put("UPLOAD_FILE_PK", uploadFile.getUploadFilePk());
			}

			/* 所屬群組(證券公司) */
			Group group = entity.getGroup();
			if (null != group && null != group.getGroupPk())
			{
				sql.append(" AND PFC.GROUP_PK = :GROUP_PK");
				parameters.put("GROUP_PK", group.getGroupPk() );
			}
		}



		if (null != paginatedList)
		{
			String orderBy = paginatedList.getSortCriterion();
			if (StringUtils.isNotEmpty(orderBy))
			{
				if (!orderBy.toUpperCase().startsWith("PFC."))
				{
					orderBy = "PFC." + orderBy;
				}

				sql.append(" ORDER BY ").append(orderBy).append(" ").append( 2==paginatedList.getSortDirection() ? "ASC" : "DESC" );
			}			
		}

		SqlQueryMapProvider sqlQueryMapProvider = new SqlQueryMapProvider(sql.toString(), parameters);

		return sqlQueryMapProvider;
	}




	public SqlQueryMapProvider getTotalSumSqlQueryMapProviderByPaymentFileContent(T entity, Date deliveryDateFrom, Date deliveryDateTo, List<String> fileFormats, List<String> constrainSecuritiesNos)

	{

		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();

		sql.append("  SELECT sum(RECORD_COUNT) as RECORD_COUNT ");
		sql.append(", SUM(CASE WHEN (sumRpt.Net_Pay - sumRpt.NET_INCOME) > 0 ");
		sql.append("	THEN(sumRpt.Net_Pay - sumRpt.NET_INCOME) ELSE 0 END) AS NET_PAY");

		sql.append(", SUM(CASE WHEN (sumRpt.NET_INCOME - sumRpt.Net_Pay) > 0 ");
		sql.append("	THEN(sumRpt.NET_INCOME - sumRpt.Net_Pay) ELSE 0 END) AS NET_INCOME");

		sql.append(", SUM(sumRpt.NET_INCOME - sumRpt.NET_PAY) AS NET_INCOME_PAY_DIFF  ");

		sql.append(", SUM(CASE WHEN (sumRpt.Net_Pay - sumRpt.NET_INCOME) > 0 ");
		sql.append("	THEN 1 ELSE 0 END) AS Net_Pay_COUNT ");

		sql.append(", SUM(CASE WHEN (sumRpt.NET_INCOME - sumRpt.Net_Pay) > 0 ");
		sql.append("	THEN 1 ELSE 0 END) AS NET_INCOME_COUNT ");

		sql.append(" from ( ");
		sql.append(" SELECT COUNT(*) AS RECORD_COUNT,sum(D.Net_Pay) net_pay ");
		sql.append(" ,sum(d.net_income) net_income ");

		sql.append(" FROM ").append(this.getTableName()).append(" D");
		sql.append(" WHERE 1= 1");

		/* 為了明細查詢功能   需增加檔案pk外的查詢條件*/		
		/* 可查詢的分公司) */
		if (CollectionUtils.isNotEmpty(constrainSecuritiesNos))
		{
			if (1 == constrainSecuritiesNos.size())
			{
				sql.append(" AND D.SECURITIES_NO = :SECURITIES_NO");
				parameters.put("SECURITIES_NO",constrainSecuritiesNos.get(0) );
			}
			else
			{
				sql.append(" AND D.SECURITIES_NO IN (:SECURITIES_NOS)");
				parameters.put("SECURITIES_NOS",constrainSecuritiesNos );
			}

		}

		/* 上傳日期 */
		if (null != deliveryDateFrom && null != deliveryDateTo)
		{
			// between 上傳起迄
			sql.append(" AND TO_CHAR(D.DELIVERY_DATE, 'YYYYMMDD') >= TO_CHAR(:DELIVERY_DATE_FROM, 'YYYYMMDD')");
			sql.append(" AND TO_CHAR(D.DELIVERY_DATE, 'YYYYMMDD') <= TO_CHAR(:DELIVERY_DATE_TO, 'YYYYMMDD')");
			parameters.put("DELIVERY_DATE_FROM", deliveryDateFrom );
			parameters.put("DELIVERY_DATE_TO", deliveryDateTo);

		}
		else if (null != deliveryDateFrom)
		{
			// >=上傳起
			sql.append(" AND TO_CHAR(D.DELIVERY_DATE, 'YYYYMMDD') >= TO_CHAR(:DELIVERY_DATE_FROM, 'YYYYMMDD') ");
			parameters.put("UPLOAD_DATE_FROM", deliveryDateFrom);

		}
		else if (null != deliveryDateTo)
		{
			// <= 上傳迄
			sql.append(" AND TO_CHAR(D.DELIVERY_DATE, 'YYYYMMDD') <= TO_CHAR(:DELIVERY_DATE_TO, 'YYYYMMDD') ");
			parameters.put("DELIVERY_DATE_TO", deliveryDateTo);

		}
		else if (null != entity && null != entity.getDeliveryDate())
		{
			// 單一日期
			sql.append(" AND TO_CHAR(D.DELIVERY_DATE, 'YYYYMMDD') = TO_CHAR(:DELIVERY_DATE, 'YYYYMMDD' )");
			parameters.put("DELIVERY_DATE", entity.getDeliveryDate());

		}

		if (null != entity)
		{

			/* 營業員 */
			if (StringUtils.isNotEmpty(entity.getTeller()))
			{
				sql.append(" AND D.TELLER like :TELLER");
				parameters.put("TELLER", entity.getTeller()+"%" );
			}

			/* 客戶戶名 */
			if (StringUtils.isNotEmpty(entity.getCustomerName()))
			{
				sql.append(" AND D.CUSTOMER_NAME like :CUSTOMER_NAME");
				parameters.put("CUSTOMER_NAME", entity.getCustomerName()+"%" );
			}

			/* 所屬檔案 */
			UploadFile uploadFile = entity.getUploadFile();
			if (null != uploadFile &&  null != uploadFile.getUploadFilePk())
			{
				sql.append(" AND D.UPLOAD_FILE_PK = :UPLOAD_FILE_PK");
				parameters.put("UPLOAD_FILE_PK", uploadFile.getUploadFilePk());
			}

			/* 所屬群組(證券公司) */
			Group group = entity.getGroup();
			if (null != group && null != group.getGroupPk())
			{
				sql.append(" AND D.GROUP_PK = :GROUP_PK");
				parameters.put("GROUP_PK", group.getGroupPk() );
			}
		}


		sql.append(" group by d.account_no");

		sql.append(" ) sumRpt ");



		SqlQueryMapProvider sqlQueryMapProvider = new SqlQueryMapProvider(sql.toString(), parameters);

		logger.debug("{}", sqlQueryMapProvider);

		return sqlQueryMapProvider;
	}




}

